﻿DECLARE @Var_SecondDeptCode VARCHAR(20)
DECLARE @Var_DoctCode VARCHAR(20)
DECLARE @Var_HideEmptyDoct BIT
SET @Var_SecondDeptCode = @SecondDeptCode_In
SET @Var_DoctCode       = @DoctCode_In
SET @Var_HideEmptyDoct  = @HideEmptyDoct_In

    SELECT RTRIM(A.KSCODE) AS SecondDeptCode
          ,RTRIM(A.KSNAME) AS SecondDeptName
          ,RTRIM(A.YSCODE) AS DoctCode
          ,RTRIM(A.YSNAME) AS DoctName
          ,B.YHFLAG AS TitleCode
          ,CASE YHFLAG WHEN 2 THEN '普通医师（初职）'
                       WHEN 3 THEN '主任医师（高职）'
                       WHEN 5 THEN '主治医师（中职）'
                       WHEN 6 THEN '副主任医师（副高）'
           END AS TitleName
          ,RTRIM(A.CHECKGROUPCODE) AS ClinicCode
          ,RTRIM(A.CHECKGROUPNAME) AS CLINICNAME
          ,CASE WHEN A.CHECKGROUPNAME LIKE '%普通挂号%' THEN 0 ELSE 1 END AS IsExpertRegistion
          ,A.CHECKMONEY1 AS RegFee
          ,A.CHECKMONEY2 AS ClinicFee
          ,A.TIMEINTERVAL AS TimeIntervalCode
          ,A.TIMEINTERVAL AS TimeIntervalName
          ,GETDATE() AS REGDATE
          ,A.SPECIALTY AS Specialty
          ,'' AS Descripton
          ,A.Address AS ClinicAddress
          ,CASE WHEN A.TOTNUM>0 THEN A.TOTNUM ELSE 999 END AS TotalSourceCount
          ,CASE WHEN A.TOTNUM>0 
                THEN CASE WHEN A.TOTNUM2 > 0 
                          THEN ISNULL(A.TOTNUM2,0) - ISNULL(C.RESERVATIONCOUNT,0) - ISNULL(D.LIVEREGISTERCOUNT,0)
                     ELSE 0
                     END
           ELSE 999
           END AS RemainSourceCount
          ,CASE WHEN A.TOTNUM>0 
                THEN CASE WHEN A.TOTNUM2 > 0 
                          THEN CASE WHEN ISNULL(A.TOTNUM2,0) - ISNULL(C.RESERVATIONCOUNT,0) - ISNULL(D.LIVEREGISTERCOUNT,0) > 0        THEN 1 
                               ELSE 0 
                               END
                     ELSE 0
                     END
           ELSE 1
           END AS AllowRegist
      FROM MZSCHEDULE A (NOLOCK)
 LEFT JOIN YSCODE B (NOLOCK) ON A.YSCODE=B.CODE
 LEFT JOIN (
            SELECT DoctCode,COUNT(*) AS RESERVATIONCOUNT 
              FROM AP_ReservationSheet (NOLOCK) 
             WHERE DelDate IS NULL AND ReservationDate=CAST(GETDATE() AS DATE) 
          GROUP BY DOCTCODE
           ) C ON A.YSCODE=C.DoctCode
 LEFT JOIN (
            SELECT X.YSCODE,SUM(CASE WHEN X.mznum>0 THEN 1 ELSE -1 END) AS LIVEREGISTERCOUNT 
              FROM mzregistersheet X (NOLOCK)
              JOIN AP_ORDERSHEET Y(NOLOCK) ON X.FPNum=Y.FPNum
             WHERE CAST(X.RegisterDate AS DATE)=CAST(GETDATE() AS DATE) AND 
                   X.deldate IS NULL AND Y.type=1 and Y.DelDate IS NULL
          GROUP BY YSCODE
           ) D ON A.YSCode=CASE WHEN D.YSCode = '' THEN '*' ELSE D.YSCode END
     WHERE A.KSCODE = @Var_SecondDeptCode AND
           A.YSCODE LIKE @Var_DoctCode AND
           (A.StopEndDate IS NULL OR CAST(GETDATE() AS DATE)<A.StopBegDate OR CAST(GETDATE() AS DATE) > A.StopEndDate) AND
           WEEKDAY=DATEPART(WEEKDAY,GETDATE()) AND
           (STATUS IS NULL OR STATUS =2) AND
           /*CHECKGROUPNAME NOT LIKE '%预约%' AND*/
           (
              @Var_HideEmptyDoct = 0 OR
              (
                @Var_HideEmptyDoct = 1 AND
                (CASE WHEN A.TOTNUM > 0 
                      THEN CASE WHEN A.TOTNUM - ISNULL(C.RESERVATIONCOUNT,0)-ISNULL(D.LIVEREGISTERCOUNT,0)>0 AND ISNULL(A.TOTNUM2,0)-ISNULL(C.RESERVATIONCOUNT,0)>0 
                                THEN 1
                           ELSE 0
                           END
                 ELSE 1
                 END = 1
                )
              )
           )